<?xml version="1.0" encoding="iso-8859-1"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>FusionCharts v3 Documentation</title>
<link rel="stylesheet" href="Style.css" type="text/css" />
</head>

<body>
<table width="98%" border="0" cellspacing="0" cellpadding="3" align="center">
  <tr> 
    <td><h2 class="pageHeader">Using FusionCharts with ColdFusion &gt; Plotting data from a database </h2></td>
  </tr>
  <tr> 
    <td valign="top" class="text"><p>In this section, we'll show you how to use FusionCharts and CFM to plot charts from data contained in a database. We'll create a pie chart to show &quot;Production by Factory&quot; using: </p>
      <ul>
        <li><span class="codeInline">dataXML</span>  method first.</li>
        <li>Thereafter, we'll convert this chart to use <span class="codeInline">dataURL</span>  method. </li>
      </ul>
      <p>We've used an MS SQL Database for this example. The Access version of this database is present in <span class="codeInline">Download Package &gt; Code &gt; CFM &gt; DB </span>folder. You can, however, use any database with FusionCharts including MS Access, Oracle, mySQL etc. </p>
      <p><strong>Before you go further with this page, we recommend you to please see the previous section &quot;Basic Examples&quot; as we start off from concepts explained in that page. </strong></p>
      <p class="highlightBlock">The code examples contained in this page are present in <span class="codeInline">Download Package &gt; Code &gt; CFM &gt; DBExample </span> folder. The Access version of database is present in <span class="codeInline">Download Package &gt; Code &gt; CFM &gt;</span> <span class="codeInline">DB</span>. </p></td>
  </tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="header">Database Structure </td>
  </tr>
  <tr>
    <td valign="top" class="text">Before we code the CFM pages to retrieve data, let's quickly have a look at the database structure. </td>
  </tr>
  <tr>
    <td valign="top" class="text"><img src="Images/Code_DB.gif" width="372" height="124" /></td>
  </tr>
  <tr>
    <td valign="top" class="text"><p>The database contains just 2 tables:</p>
      <ol>
        <li><span class="codeInline">Factory_Master</span>: To store the name and id of each factory</li>
        <li><span class="codeInline">Factory_Output</span>: To store the number of units produced by each factory for a given date.</li>
      </ol>
    <p>For demonstration, we've fed some dummy data in the database. Let's now shift our attention to the CFM page that will interact with the database, fetch data and then render a chart. </p></td>
  </tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="header">Building the CFM Page for dataXML Method </td>
  </tr>
  <tr>
    <td valign="top" class="text">The CFM page for <span class="codeInline">dataXML</span> method example is named as <span class="codeInline">BasicDBExample.cfm</span> (in <span class="codeInline">DBExample</span> folder). It contains the following code: </td>
  </tr>
  <tr>
    <td valign="top" class="codeBlock"><p>&lt;HTML&gt;<br />
      &nbsp;&nbsp;&nbsp;&lt;HEAD&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FusionCharts - Database Example<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;/HEAD&gt;<br />
&lt;cfinclude template=&quot;../Includes/FusionCharts.cfm&quot;&gt;<br />
&lt;BODY&gt;<br />
&nbsp;&nbsp;&nbsp;<span class="codeComment">&lt;!---<br />
  &nbsp;&nbsp;&nbsp;In this example, we show how to connect FusionCharts to a database.  &nbsp;&nbsp;&nbsp;<br />
  &nbsp;&nbsp;&nbsp;---&gt;</span><br />
  <br />
  <span class="codeComment">&nbsp;&nbsp;&nbsp;&lt;!--- Generate the chart element ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&lt;cfset strXML = &quot;&lt;chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'&gt;&quot;&gt;</p>
      <p> <span class="codeComment">&nbsp;&nbsp;&nbsp;&lt;!--- Iterate through each factory ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&lt;cfquery name=&quot;qry&quot; datasource=&quot;dev&quot;&gt;<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select * from Factory_Master<br />
&nbsp;&nbsp;&nbsp;&lt;/cfquery&gt;<br />
  <br />
&nbsp;&nbsp;&nbsp;&lt;cfloop query=&quot;qry&quot;&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfset factoryID = qry.FactoryId&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfset factoryName = qry.FactoryName&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;!--- Now get details for this factory ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfquery name=&quot;qryDetails&quot; datasource=&quot;dev&quot;&gt;<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select sum(Quantity) as TotOutput from Factory_Output where FactoryId=#factoryID#<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/cfquery&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;!--- Generate &lt;set label='..' value='..'/&gt; ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfset strXML = strXML &amp; &quot;&lt;set label='#factoryName#' value='#qryDetails.totOutput#'/&gt;&quot;&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;/cfloop&gt;</p>
      <p> <span class="codeComment">&nbsp;&nbsp;&nbsp;&lt;!--- Finally, close &lt;chart&gt; element ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&lt;cfset strXML = strXML &amp; &quot;&lt;/chart&gt;&quot;&gt;<br />
  <br />
  <span class="codeComment">&nbsp;&nbsp;&nbsp;&lt;!--- Create the chart - Pie 3D Chart with data from strXML ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&lt;cfoutput&gt;#renderChart(&quot;../../FusionCharts/Pie3D.swf&quot;, &quot;&quot;, strXML, &quot;FactorySum&quot;, 600, 300, false, false)#&lt;/cfoutput&gt;<br />
&lt;/BODY&gt;<br />
&lt;/HTML&gt;</p></td>
  </tr>
  <tr>
    <td valign="top" class="text"><p>The following actions are taking place in this code:</p>
      <ol>
        <li>We first include <span class="codeInline">FusionCharts.js</span> JavaScript class and <span class="codeInline">FusionCharts.cfm</span> , to enable easy embedding of FusionCharts.</li>
        <li>We generate the XML data document by iterating through the recordset and store it in <span class="codeInline">strXML</span> variable. </li>
        <li>Finally, we render the chart using <span class="codeInline">renderChart()</span> method and pass <span class="codeInline">strXML</span> as <span class="codeInline">dataXML</span>. </li>
      </ol>
    <p>When you now run the code, you'll get an output as under: </p></td>
  </tr>
  <tr>
    <td valign="top" class="text"><img src="Images/Code_DBOut.jpg" width="572" height="273" class="imageBorder" /></td>
  </tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="header">Converting the example to use dataURL method </td>
  </tr>
  <tr>
    <td valign="top" class="text"><p>Let's now convert this example to use dataURL method. As previously explained, in dataURL mode, you need two pages:</p>
      <ol>
        <li><strong>Chart Container Page</strong> - The page which embeds the HTML code to render the chart. This page also tells the chart where to load the data from. We'll name this page as <span class="codeInline">Default.cfm</span>. </li>
        <li><strong>Data Provider Page</strong> - This page provides the XML data to the chart. We'll name this page as <span class="codeInline">PieData.cfm</span></li>
      </ol>
      <p class="highlightBlock">The pages in this example are contained in<span class="codeInline"> Download Package &gt; Code &gt; CFM &gt; DB_dataURL</span> folder. </p>    </td>
  </tr>
  <tr>
    <td valign="top" class="text">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" class="header">Chart Container Page - <span class="codeInline">Default.cfm </span></td>
  </tr>
  <tr>
    <td valign="top" class="text"><span class="codeInline">Default.cfm</span> contains the following code to render the chart: </td>
  </tr>
  <tr>
    <td valign="top" class="codeBlock">&lt;HTML&gt;<br />
      &nbsp;&nbsp;&nbsp;&lt;HEAD&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FusionCharts - dataURL and Database Example<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;/HEAD&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;cfinclude template=&quot;../Includes/FusionCharts.cfm&quot;&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;BODY&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;!---<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;In this example, we show how to connect FusionCharts to a database <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;using dataURL method. In our other examples, we've used dataXML method<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where the XML is generated in the same page as chart. Here, the XML data<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for the chart would be generated in PieData.cfm.<br />
  <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;To illustrate how to pass additional data as querystring to dataURL, <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;we've added an animate property, which will be passed to PieData.cfm. <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PieData.cfm would handle this animate property and then generate the <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;XML accordingly.
  <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;---&gt; </span>
<p><span class="codeComment">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;!--- <br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set DataURL with animation property to 1<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOTE: It's necessary to encode the dataURL if you've added parameters to it<br />
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfset strDataURL = URLEncodedFormat(&quot;PieData.cfm?animate=1&quot;)&gt;<br />
  <br />
  <span class="codeComment">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;!--- Create the chart - Pie 3D Chart with dataURL as strDataURL ---&gt;</span><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;cfoutput&gt;#renderChart(&quot;../../FusionCharts/Pie3D.swf&quot;, strDataURL, &quot;&quot;, &quot;FactorySum&quot;, 600, 300, false, false)#&lt;/cfoutput&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;/BODY&gt;<br />
&lt;/HTML&gt;</p></td>
  </tr>
  <tr>
    <td valign="top" class="text"><p>In the above code, we're:</p>
      <ol>
        <li>Including <span class="codeInline">FusionCharts.js</span> JavaScript class and <span class="codeInline">FusionCharts.cfm</span></li>
        <li>Create the <span class="codeInline">dataURL</span> string and store it in <span class="codeInline">strDataURL</span> variable. We append a dummy propery <span class="codeInline">animate</span> to show how to pass parameters to <span class="codeInline">dataURL</span>. After building the <span class="codeInline">dataURL</span>, we encode it using <span class="codeInline">encodeDataURL</span> function defined in <span class="codeInline">FusionCharts.cfm</span>. </li>
        <li>Finally, we render the chart using <span class="codeInline">renderChart()</span> method and set <span class="codeInline">dataURL</span> as <span class="codeInline">strDataURL</span>. </li>
      </ol>    </td>
  </tr>
  <tr>
    <td valign="top" class="header">Creating the data provider page <span class="codeInline">PieData.cfm </span></td>
  </tr>
  <tr>
    <td valign="top" class="text">PieData.cfm contains the following code to output XML Data: </td>
  </tr>
  <tr>
    <td valign="top" class="codeBlock"><p>&lt;cfsilent&gt;<br />
        <span class="codeComment">&lt;!---<br />
This page generates the XML data for the Pie Chart contained in<br />
Default.cfm. 
<br />
---&gt;</span><br />
<br />
<span class="codeComment">&lt;!--- Default.cfm has passed us a property animate. We request that. ---&gt;</span><br />
&lt;cfparam name=&quot;URL.animate&quot; default=&quot;1&quot;&gt;<br />
&lt;cfset animateChart = URL.animate&gt;<br />
<br />
<span class="codeComment">&lt;!--- Generate the chart element ---&gt;</span><br />
&lt;cfset strXML = &quot;&lt;chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' &quot; &amp; animateChart &amp; &quot;'&gt;&quot;&gt;<br />
<br />
<span class="codeComment">&lt;!--- Iterate through each factory ---&gt;</span><br />
&lt;cfquery name=&quot;qry&quot; datasource=&quot;dev&quot;&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span>select * from Factory_Master<br />
&lt;/cfquery&gt;<br />
<br />
&lt;cfloop query=&quot;qry&quot;&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span>&lt;cfset factoryID = qry.FactoryId&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span>&lt;cfset factoryName = qry.FactoryName&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;&lt;!--- Now get details for this factory ---&gt;</span><br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span>&lt;cfquery name=&quot;qryDetails&quot; datasource=&quot;dev&quot;&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span><span class="codeComment">&nbsp;&nbsp;&nbsp;</span>select sum(Quantity) as TotOutput from Factory_Output where FactoryId=#factoryID#<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span>&lt;/cfquery&gt;<br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;&lt;!--- Generate &lt;set label='..' value='..'/&gt; ---&gt;</span><br />
<span class="codeComment">&nbsp;&nbsp;&nbsp;</span>&lt;cfset strXML = strXML &amp; &quot;&lt;set label='#factoryName#' value='#qryDetails.totOutput#' /&gt;&quot;&gt;<br />
&lt;/cfloop&gt;</p>
      <p> <span class="codeComment">&lt;!--- Finally, close &lt;chart&gt; element ---&gt;</span><br />
&lt;cfset strXML = strXML &amp; &quot;&lt;/chart&gt;&quot;&gt;<br />
  <span class="codeComment"><br />
&lt;!--- <br />
  Just write out the XML data<br />
  NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER<br />
  ---&gt;</span><br />
&lt;/cfsilent&gt;<br />
&lt;cfoutput&gt;#strXML#&lt;/cfoutput&gt;<br />
      </p>
    </td>
  </tr>
  <tr>
    <td valign="top" class="text"><p>In the above page:</p>
      <ol>
        <li>We first request the animate property which has been passed to it (from <span class="codeInline">dataURL</span>)</li>
        <li>We generate the data and store it in <span class="codeInline">strXML</span> variable</li>
        <li>Finally, we write this data to output stream without any HTML tags. </li>
      </ol>
    <p>When you view this page, you'll get the same output as before. </p></td>
  </tr>
</table>
</body>
</html>
